package org.lq.shopping.dao.impl;

import java.math.BigDecimal;
import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.log4j.Logger;
import org.lq.shopping.dao.ShopShengqDao;
import org.lq.shopping.entity.ShopShengq;
import org.lq.shopping.util.DruidUtil;
/**
 * 
 * @author 和鸿雁、杜雪坤、孙帅旗
 * @时间 2018年9月26日
 * Shopping_LQ
 */
public class ShopShengqDaoImpl implements ShopShengqDao {

	Logger log = Logger.getLogger(ShopShengqDaoImpl.class);
	QueryRunner qr = new QueryRunner(DruidUtil.getDataSource());
	/**
	 * 添加售后
	 */
	@Override
	public int add(ShopShengq t) {
		log.info("这是售后表的添加");
		int num = 0;
		String sql = "insert into shop_shengq(after_id, order_id, cause, issue, after_img, voucher, mode_return, address_get, address_put, username, user_id, after_date)values(shengq_seq.nextval, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		log.debug("传入:"+t.toString());
		log.debug("SQL:"+sql);
		try {
			num = qr.execute(sql, t.getOrder_id(),t.getCause(),t.getIssue(),t.getAfter_img(),t.getVoucher(),t.getMode_return(),t.getAddress_get(),t.getAddress_put(),t.getUsername(),t.getUser_id(),t.getAfter_date());
		} catch (SQLException e) {
			log.error("这是售后表添加的异常信息");
		}
		return num;
	}

	/**
	 * 修改
	 */
	@Override
	public int update(ShopShengq t) {
		log.info("这是售后表的修改");
		int num = 0;
		String sql = "update shop_shengq set order_id = ?,cause = ?,issue = ?,after_img = ?,voucher = ?,mode_return = ?,address_get = ?,address_put = ?, username = ?,user_id = ?,after_date = ? where after_id = ?";
		log.debug("传入:"+t.toString());
		log.debug("SQL:"+sql);
		try {
			num = qr.execute(sql, t.getOrder_id(),t.getCause(),t.getIssue(),t.getAfter_img(),t.getVoucher(),t.getMode_return(),t.getAddress_get(),t.getAddress_put(),t.getUsername(),t.getUser_id(),t.getAfter_date(),t.getAfter_id());
		} catch (SQLException e) {
			log.error("这是售后表修改的异常信息");
		}
		return num;
	}

	/**
	 * 删除
	 */
	@Override
	public int delete(int id) {
		log.info("这是售后表的删除");
		int num = 0;
		String sql = "delete shop_shengq where after_id = ?";
		log.debug("传入:"+id);
		log.debug("SQL:"+sql);
		try {
			num = qr.execute(sql, id);
		} catch (SQLException e) {
			log.error("这是售后表删除的异常信息");
		}
		return num;
	}

	/**
	 * 查询全部
	 */
	@Override
	public List<ShopShengq> findAll() {
		log.info("查询所有售后信息");
		List<ShopShengq> list = null;
		String sql = "select * from shop_shengq";
		log.debug("SQL:"+sql);
		try {
			list = qr.query(sql, new BeanListHandler<>(ShopShengq.class));
		} catch (SQLException e) {
			log.error("这是售后表查询全部异常信息");
		}
		return list;
	}

	/**
	 * 根据售后id查询
	 */
	@Override
	public ShopShengq findById(int id) {
		log.info("根据售后id查询");
		ShopShengq sheng = null;
		String sql = "select * from shop_shengq where after_id = ?";
		log.debug("传入:"+id);
		log.debug("SQL:"+sql);
		try {
			sheng = qr.query(sql, new BeanHandler<>(ShopShengq.class),id);
		} catch (SQLException e) {
			log.error("这是根据售后id查询售后表异常信息");
		}
		return sheng;
	}

	/**
	 * 根据订单ID查询售后表
	 */
	@Override
	public List<ShopShengq> getListByOrderId(int order_id) {
		log.info("根据订单ID查询售后表");
		List<ShopShengq> list = null;
		String sql = "select * from shop_shengq where order_id = ?";
		log.debug("传入:"+order_id);
		log.debug("SQL:"+sql);
		try {
			list = qr.query(sql, new BeanListHandler<>(ShopShengq.class),order_id);
		} catch (SQLException e) {
			log.error("这是根据订单ID查询售后表异常信息");
		}
		return list;
	}

	/**
	 * 根据指定的列名模糊查询
	 */
	@Override
	public List<ShopShengq> LikeColumnByValue(String columName, Object value) {
		log.info("根据列名查询售后表");
		List<ShopShengq> list = null;
		String sql = "select * from shop_shengq where "+columName+" like ? ";
		log.debug("传入的列名:"+columName+"\t传入的值:"+value);
		log.debug("SQL:"+sql);
		try {
			list = qr.query(sql, new BeanListHandler<>(ShopShengq.class),"%"+value+"%");
		} catch (SQLException e) {
			log.error("根据列名查询售后表异常信息");
		}
		return list;
	}

	/**
	 * 获取总行数
	 */
	@Override
	public int getCount() {
		log.info("获取售后表的总行数");
		int count = 0;
		String sql = "seclect count(1) from shop_shengq";
		log.debug("SQL:"+sql);
		try {
			BigDecimal bd = qr.query(sql, new ScalarHandler<>());
			count = bd.intValue();
		} catch (SQLException e) {
			log.error("获取售后表的总行数异常信息");
		}
		return count;
	}

	/**
	 * 分页
	 */
	@Override
	public List<ShopShengq> findPage(int pageIndex, int pageSize) {
		log.info("售后表的分页查询");
		List<ShopShengq> list = null;
		String sql = " select * from " + 
				" (select rownum r,s.* from" + 
				" (select * from shop_shengq order by after_id) s" + 
				" where rownum <= ?)" + 
				" where r > ?";
		log.debug("传入的页数:"+pageIndex+"\t传入页面大小:"+pageSize);
		log.debug("SQL:"+sql);
		int max = pageIndex * pageSize;
		int min = (pageIndex - 1) * pageSize;
		try {
			list = qr.query(sql, new BeanListHandler<>(ShopShengq.class),max,min);
		} catch (SQLException e) {
			log.error("售后表的分页查询的异常信息");
		}
		return list;
	}

	@Override
	public List<ShopShengq> findPageByUser(int uid, int pageIndex, int pageSize) {
		log.info("用户自己查询售后表的分页查询");
		List<ShopShengq> list = null;
		String sql = " select * from " + 
				" (select rownum r,s.* from" + 
				" (select * from shop_shengq where user_id = ? order by after_id) s" + 
				" where rownum <= ?)" + 
				" where r > ?";
		log.debug("传入:"+uid+"传入的页数:"+pageIndex+"\t传入页面大小:"+pageSize);
		log.debug("SQL:"+sql);
		int max = pageIndex * pageSize;
		int min = (pageIndex - 1) * pageSize;
		try {
			list = qr.query(sql, new BeanListHandler<>(ShopShengq.class),uid,max,min);
		} catch (SQLException e) {
			log.error("用户自己查询售后表的分页查询的异常信息");
		}
		return list;
	}

	@Override
	public List<ShopShengq> getListByUId(int uid) {
		log.info("根据用户ID查询售后表");
		List<ShopShengq> list = null;
		String sql = "select * from shop_shengq where order_id = ?";
		log.debug("传入:"+uid);
		log.debug("SQL:"+sql);
		try {
			list = qr.query(sql, new BeanListHandler<>(ShopShengq.class),uid);
		} catch (SQLException e) {
			log.error("这是根据用户ID查询售后表异常信息");
		}
		return list;
	}

}
